Antipattern: Execute Unverified Input As Code
Let’s learn what problems occur when someone tries to execute unverified input as a code.
We'll cover the following
An SQL injection happens when we interpolate some content into an SQL query string, and the content modifies the syntax of our query in ways we didn’t intend. In the classic example of SQL Injection, the value we interpolate into our string finishes the SQL statement and executes a second complete statement. For instance, if the value of the ＄bug_id variable is 1234; DELETE FROM Bugs, the resulting SQL shown earlier would look like this:
After this query is executed, we will lose the table. Let’s see the output in the next playground.
The query has been executed, but it returns no results.
This type of SQL Injection can be spectacular.
Another example with a similar case is illustrated in the figure below.
Usually, these flaws are more subtle but still dangerous.
Accidents may happen#
Let’s suppose we are writing a web interface to view the bugs database, and one page allows us to view a project based on its name:
<?php
＄project_name = ＄_REQUEST["name"];
＄sql = "SELECT * FROM Projects WHERE project_name = '＄project_name'";
?>
The trouble begins when our team is hired to develop software for O’Hare International Airport in Chicago. We naturally give the project a name like “O’Hare.” How do we submit a request to view the project in our web application?
http://bugs.example.com/project/view.php?name=O'Hare
Our PHP code takes the value of that request parameter and interpolates it into the SQL query, but it produces a query that neither we nor the user intended:
Because a string is terminated by the first quote character it finds, the resulting expression contains a short string, 'O', followed by some extra characters, Hare', which makes no sense in this context. The database can only report this as a syntax error. This is an honest accident. The risk of anything bad happening is low because a statement with a syntax error can’t be executed. The greater risk is that the statement executes without error but does something we didn’t intend.
The top web security threat#
SQL Injection becomes a greater threat when an attacker is able to use this to manipulate our SQL statements. For example, our application may allow a user to change their password:
<?php
＄password = ＄_REQUEST["password"];
＄userid = ＄_REQUEST["userid"];
＄sql = "UPDATE Accounts SET password_hash = SHA2('＄password') WHERE account_id = ＄userid";
?>
A clever attacker who can guess how the request parameters are used in our SQL statement can send a carefully chosen string to exploit it:
http://bugs.example.com/setpass?password=xyzzy&userid=123 OR TRUE
After interpolating the string from the userid parameter into our SQL expression, the string has changed the syntax of the statement. Now it changes the password for every account in the database, not for one specific account:
This is the key to understanding SQL Injection and also how to combat it: SQL Injection works by changing the syntax of the SQL statement before the statement is parsed. As long as we insert dynamic portions to the statement before it’s parsed, we have a risk of SQL Injection.
There are countless ways a maliciously chosen string could alter the behavior of our SQL statements. It’s limited only by the imagination of the attacker and our ability to protect our SQL statements.
